package dao;

import beans.ProductoTo;
import beans.UsuarioTo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.ArrayList;
import negocio.UsuarioManager;
import negocio.UsuarioManagerException;

import utildb.GenericDAO;
import utildb.Transaction;
import utildb.TransactionException;
import utildb.Util;


public class ProductoDAO extends GenericDAO {
    
    public ProductoDAO(Transaction transacction) {
        super(transacction);
    }

    public void create(ProductoTo to) throws TransactionException {
            try {
                    int i = 1;
                    Connection c;
                    PreparedStatement statement = null;
                    c = (Connection) transaction.getConnection();

                    statement = c.prepareStatement("insert into mySql.Vemc_Prod " +
                                 " (ccod_Prod, cruc_Prov, cnom_Prod, cnom_Prod_Ingl, " +
                                  " cdes_Prod, cdes_Prod_Ingl, nest_Prod, nsit_Prod, " +
                                  " ctip_Mone_Comp, npre_Comp, ctip_Mone_Vent, npre_Vent, " +
                                  " nsto_Prod, nnum_Dias_Entr, ccat_Prod, ctip_Prod, " +
                                  " daud_Fec_Modi, caud_Usu_Modi, daud_Fec_Crea, " +
                                  " caud_Usu_Crea ) " +
                         " values (?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?)");
                                            // 1 2 3 4 5  6 7 8 9 10 11 12 13 14
                    statement.setString(i++, to.getCcod_Prod());
                    statement.setString(i++, to.getCruc_Prov());
                    statement.setString(i++, to.getCnom_Prod());
                    statement.setString(i++, to.getCnom_Prod_Ingl());
                    statement.setString(i++, to.getCdes_Prod());
                    //
                    statement.setString(i++, to.getCdes_Prod_Ingl());
                    statement.setInt(i++, to.getNest_Prod());
                    statement.setInt(i++, to.getNsit_Prod());
                    statement.setString(i++, to.getCtip_Mone_Comp());
                    statement.setDouble(i++, to.getNpre_Comp());
                    //
                    statement.setString(i++, to.getCtip_Mone_Vent());
                    statement.setDouble(i++, to.getNpre_Vent());
                    statement.setDouble(i++, to.getNsto_Prod());
                    statement.setDouble(i++, to.getNnum_Dias_Entr());
                    statement.setString(i++, to.getCcat_Prod());
                    //
                    statement.setString(i++, to.getCtip_Prod());
                    statement.setDate(i++, to.getDaud_Fec_Modi());
                    statement.setString(i++, to.getCaud_Usu_Modi());
                    statement.setDate(i++, to.getDaud_Fec_Crea());
                    statement.setString(i++, to.getCaud_Usu_Crea());
                
                    int n = statement.executeUpdate();
                    statement.close();
                    if (n == 0)
                            throw new TransactionException("No se pudo crear el producto");
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
    }

    public void eliminar(String codProd ) throws TransactionException {
           throw new TransactionException("No soportado");
    }
    
    public void eliminarProducto(String rucProv, String codProd ) throws TransactionException {
            try {
                    int i = 1;
                    Connection c;
                    PreparedStatement statement = null;
                    c = (Connection) transaction.getConnection();
                    statement = c.prepareStatement("delete from mySql.VEMC_Prod "
                            + " Where cRuc_Prov = ? "
                              + " And cCod_Prod = ? " );

                    statement.setString(i++, rucProv );
                    statement.setString(i++, codProd );
                    
                    int n = statement.executeUpdate();
                    statement.close();
                    if (n == 0)
                            throw new TransactionException("No se pudo eliminar el producto");                   
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
    }
    
    public ArrayList buscarProductos(String crucProv, String cCodProd )
                     throws TransactionException {
            Connection c;
            UsuarioManager pmgUser;
            pmgUser = new UsuarioManager();
            //
            Statement statement = null;
            String ccadSql = "";
            String cCodProdValida = "%";
            String cCodCateValida = "%";
            String cCodTipoValida = "%";
            String cDesProdValida = "%";
           //
            if (cCodProd.equals("%")) {
                cCodProdValida = cCodProd; }
            else {
                cCodProdValida  = Util.ignoraSQLInjection(cCodProd);
            }

            // 
            ArrayList listaProductos = new ArrayList();
            try {
                    c = transaction.getConnection();
                    Integer tipobd = transaction.getTipobd();
                    statement = c.createStatement();
                    ccadSql = "select a.ccod_prod, a.cruc_Prov, " +
                                   " a.cnom_prod, a.cdes_prod, " +
                                   " b.cnom_tabl Categoria, c.cnom_tabl Tipo, " +
                                   " a.nest_prod, a.cTip_mone_comp, " +
                                   " a.ctip_mone_vent, d.ccod_refe Moneda, " +
                                   " a.npre_comp, a.npre_vent, a.ccat_Prod, " +
                                   " a.ctip_Prod, " +
                                   " a.daud_fec_modi, a.caud_usu_modi, " +
                                   " a.daud_fec_crea, a.caud_usu_crea " +
                              " from mySql.VEMC_Prod a, mySql.VEMC_Tabl b, " +
                                   " mySql.VEMC_Tabl c, mySql.VEMC_Tabl d " +
                             " Where a.cCat_Prod = b.cCod_Tipo " +
                               " And a.cTip_Prod = c.cCod_Tipo " +
                               " And a.cTip_Mone_Comp = d.cCod_Tipo " +
                               " And a.cRuc_Prov like '" + crucProv + "' " +
                               " And a.ccod_prod like '" + cCodProdValida + "' " +
                               " And a.ccat_Prod like '" + cCodCateValida + "' " +
                               " And a.ctip_Prod like '" + cCodTipoValida + "' " +
                               " And ( a.cDes_Prod like '" + cDesProdValida + "' Or " +
                                     " a.cNom_Prod like '" + cDesProdValida + "' Or " +
                                     " b.cnom_tabl like '" + cDesProdValida + "' Or " +
                                     " c.cnom_tabl like '" + cDesProdValida + "' ) " +
                            "  Order By 1"; 
                
                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
                        UsuarioTo proveedor;
                        ProductoTo prod = new ProductoTo();
                        prod.setCcod_Prod( rs.getString("ccod_prod") );
                        prod.setCruc_Prov( rs.getString("cruc_Prov") );
                        prod.setCnom_Prod( rs.getString("cnom_prod"));
                        prod.setCdes_Prod( rs.getString("cdes_prod"));
                        prod.setNest_Prod( rs.getInt("nest_prod"));
                        prod.setCtip_Mone_Comp( rs.getString("ctip_mone_comp"));
                        prod.setCdes_Mone_Comp( rs.getString("moneda"));
                        prod.setNpre_Comp( rs.getDouble("npre_comp"));
                        prod.setCtip_Mone_Vent( rs.getString("ctip_mone_vent"));
                        prod.setCdes_Mone_Vent( rs.getString("moneda"));
                        prod.setNpre_Vent( rs.getDouble("npre_vent"));
                        prod.setCcat_Prod( rs.getString("ccat_prod"));  
                        prod.setCdes_Cate( rs.getString("Categoria"));
                        prod.setCtip_Prod( rs.getString("ctip_prod"));  
                        prod.setCdes_Tipo( rs.getString("Tipo"));
                        prod.setDaud_Fec_Modi( rs.getDate("daud_fec_modi"));
                        prod.setCaud_Usu_Modi( rs.getString("caud_usu_modi"));
                        prod.setDaud_Fec_Crea( rs.getDate("daud_fec_crea"));
                        prod.setCaud_Usu_Crea( rs.getString("caud_usu_crea"));
                        try {
                            proveedor = pmgUser.buscarProveedor(rs.getString("cruc_Prov"));
                            prod.setCnom_Prov(proveedor.getCnom_Usua());
                            listaProductos.add(prod);
                        } catch (UsuarioManagerException ex) {
                            prod.setCnom_Prov("");
                        }
                        //
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return listaProductos;
    }

    public ProductoTo getProducto(String cRucProv, String cCodProd )
                                  throws TransactionException {
            Connection c;
            UsuarioManager pmgUser;
            pmgUser = new UsuarioManager();
            //
            Statement statement = null;
            String ccadSql = "";
            ProductoTo prod = new ProductoTo();
            //
            cRucProv = Util.ignoraSQLInjection(cRucProv);
            cCodProd = Util.ignoraSQLInjection(cCodProd);
            try {
                    c = transaction.getConnection();
                    statement = c.createStatement();
                    ccadSql = "select a.ccod_prod, a.cruc_Prov, " +
                                   " a.cnom_prod, a.cdes_prod, " +
                                   " b.cnom_tabl Categoria, c.cnom_tabl Tipo, " +
                                   " a.nest_prod, a.cTip_mone_comp, " +
                                   " a.ctip_mone_vent, d.ccod_refe Moneda, " +
                                   " a.npre_comp, a.npre_vent, a.ccat_Prod, " +
                                   " a.ctip_Prod, a.nnum_Dias_Entr " +
                              " from mySql.VEMC_Prod a, mySql.VEMC_Tabl b, " +
                                   " mySql.VEMC_Tabl c, mySql.VEMC_Tabl d " +
                             " Where a.cCat_Prod = b.cCod_Tipo " +
                               " And a.cTip_Prod = c.cCod_Tipo " +
                               " And a.cTip_Mone_Comp = d.cCod_Tipo " +
                               " And a.cRuc_Prov = '" + cRucProv + "' " +
                               " And a.ccod_prod = '" + cCodProd + "' " +
                            "  Order By 1";

                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
                        UsuarioTo proveedor;
                        prod.setCcod_Prod( rs.getString("ccod_prod") );
                        prod.setCruc_Prov( rs.getString("cruc_Prov") );
                        prod.setCnom_Prod( rs.getString("cnom_prod"));
                        prod.setCdes_Prod( rs.getString("cdes_prod"));
                        prod.setNest_Prod( rs.getInt("nest_prod"));
                        prod.setCtip_Mone_Comp( rs.getString("ctip_mone_comp"));
                        prod.setCdes_Mone_Comp( rs.getString("moneda"));
                        prod.setNpre_Comp( rs.getDouble("npre_comp"));
                        prod.setCtip_Mone_Vent( rs.getString("ctip_mone_vent"));
                        prod.setCdes_Mone_Vent( rs.getString("moneda"));
                        prod.setNpre_Vent( rs.getDouble("npre_vent"));
                        prod.setCcat_Prod( rs.getString("ccat_prod"));
                        prod.setCdes_Cate( rs.getString("Categoria"));
                        prod.setCtip_Prod( rs.getString("ctip_prod"));
                        prod.setCdes_Tipo( rs.getString("Tipo"));
                        prod.setNnum_Dias_Entr( rs.getInt("nnum_Dias_Entr"));
                        try {
                            proveedor = pmgUser.buscarProveedor(rs.getString("cruc_Prov"));
                            prod.setCnom_Prov(proveedor.getCnom_Usua());
                        } catch (UsuarioManagerException ex) {
                            prod.setCnom_Prov("");
                        }
                        //
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return prod;
    }

    public ArrayList ejecutarBuscarProductos(String crucProv,
                                             String cCodProd,
                                             String cCodCate,
                                             String cTxtBuscar,
                                             double nPreProd1,
                                             double nPreProd2
                                             ) throws TransactionException {
            Connection c;
            UsuarioManager pmgUser;
            pmgUser = new UsuarioManager();
            //
            Statement statement = null;
            String ccadSql = "";
            if (nPreProd2==0) {
                nPreProd2 = 9999999;
            }
            //
            ArrayList listaProductos = new ArrayList();
            try {
                    c = transaction.getConnection();
                    statement = c.createStatement();
                    ccadSql = "select a.ccod_prod, a.cruc_Prov, e.cnom_usua, " +
                                   " a.cnom_prod, a.cdes_prod, " +
                                   " b.cnom_tabl Categoria, c.cnom_tabl Tipo, " +
                                   " a.nest_prod, a.cTip_mone_comp, " +
                                   " a.ctip_mone_vent, d.ccod_refe Moneda, " +
                                   " a.npre_comp, a.npre_vent, a.ccat_Prod, " +
                                   " a.ctip_Prod, a.nnum_Dias_Entr, " +
                                   " a.daud_fec_modi, a.caud_usu_modi, " +
                                   " a.daud_fec_crea, a.caud_usu_crea " +
                              " from mySql.VEMC_Prod a, mySql.VEMC_Tabl b, " +
                                   " mySql.VEMC_Tabl c, mySql.VEMC_Tabl d, " +
                                   " mySql.gemc_usua e " +
                             " Where a.cCat_Prod = b.cCod_Tipo " +
                               " And a.cTip_Prod = c.cCod_Tipo " +
                               " And a.cTip_Mone_Comp = d.cCod_Tipo " +
                               " And a.cRuc_Prov = e.cRuc_Usua " +
                               " And a.nEst_Prod = 2 " +
                               " And a.cRuc_Prov like '" + crucProv + "' " +
                               " And a.ccod_prod like '" + cCodProd + "' " +
                               " And a.ccat_Prod like '" + cCodCate + "' " +
                               " And a.nSto_Prod > 0 " +
                               " And a.npre_Vent > 0 " +
                               " And a.npre_Vent >= " + nPreProd1 + " " +
                               " And a.npre_Vent <= " + nPreProd2 + " " +
                               " And ( a.cDes_Prod like '" + cTxtBuscar + "' Or " +
                                     " a.cNom_Prod like '" + cTxtBuscar + "' Or " +
                                     " e.cNom_Usua like '" + cTxtBuscar + "' Or " +
                                     " b.cnom_tabl like '" + cTxtBuscar + "' Or " +
                                     " c.cnom_tabl like '" + cTxtBuscar + "' ) " +
                            "  Order By 1";
                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
                        ProductoTo prod = new ProductoTo();
                        prod.setCcod_Prod( rs.getString("ccod_prod") );
                        prod.setCruc_Prov( rs.getString("cruc_Prov") );
                        prod.setCnom_Prod( rs.getString("cnom_prod"));
                        prod.setCdes_Prod( rs.getString("cdes_prod"));
                        prod.setNest_Prod( rs.getInt("nest_prod"));
                        prod.setCtip_Mone_Comp( rs.getString("ctip_mone_comp"));
                        prod.setCdes_Mone_Comp( rs.getString("moneda"));
                        prod.setNpre_Comp( rs.getDouble("npre_comp"));
                        prod.setCtip_Mone_Vent( rs.getString("ctip_mone_vent"));
                        prod.setCdes_Mone_Vent( rs.getString("moneda"));
                        prod.setNpre_Vent( rs.getDouble("npre_vent"));
                        prod.setCcat_Prod( rs.getString("ccat_prod"));
                        prod.setCdes_Cate( rs.getString("Categoria"));
                        prod.setCtip_Prod( rs.getString("ctip_prod"));
                        prod.setCdes_Tipo( rs.getString("Tipo"));
                        prod.setDaud_Fec_Modi( rs.getDate("daud_fec_modi"));
                        prod.setCaud_Usu_Modi( rs.getString("caud_usu_modi"));
                        prod.setDaud_Fec_Crea( rs.getDate("daud_fec_crea"));
                        prod.setCaud_Usu_Crea( rs.getString("caud_usu_crea"));
                        prod.setCnom_Prov( rs.getString("cnom_usua"));
                        prod.setNnum_Dias_Entr( rs.getInt("nnum_Dias_Entr"));
                        prod.setProd_sele(0);
                        listaProductos.add(prod);
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return listaProductos;
    }

    public boolean existeProducto(String crucProv, String cCodProd )
                     throws TransactionException {
            Connection c;
            Statement statement = null;
            String ccadSql = "";
            boolean lb_okey = false;
            //
            if ((cCodProd == null) || cCodProd.length() == 0 ) {
               throw new TransactionException("Código no valido");
            }
            ArrayList listaProductos = new ArrayList();
            try {
                    c = transaction.getConnection();
                    statement = c.createStatement();
                    ccadSql = "select cnom_prod " +
                              " from mySql.VEMC_Prod " +
                             " Where ccod_prod = '" + cCodProd + "' " +
                               " And cruc_Prov = '" + crucProv + "'";

                    ResultSet rs = statement.executeQuery(ccadSql);
                    while (rs.next()) {
                        lb_okey = true;
                    }
                    rs.close();
                    statement.close();
            } catch (SQLException e1) {
                    throw new TransactionException(e1);
            }
            return lb_okey;
    }

}
